
import json
import traceback
import pandas as pd
from tqdm import tqdm
from django.db import IntegrityError, transaction
from control.models import User
from literature.models import *
from basic_component_related_management.models import *
from dataset.models import *
from unit_management.models import *


def convert_file_to_json():
    # 单位对齐
    file_path = "files/basic_data_init_final_v2/dechema/DECHEMA单位-物性-相态-标签对齐.xlsx"
    sheet_name_unit = "DECHEMA单位对齐表 "
    unit_df = pd.read_excel(
        file_path, sheet_name=sheet_name_unit, header=0, dtype=str)
    unit_select = unit_df.iloc[:, :].fillna("")
    unit_json = []
    for index, row in unit_select.iterrows():
        unit_json.append({
            "dechema_unit": row["Unit_set2"],
            "sei_unit": row["nUnitIDCombine.1"],
            "sei_unit_id": row["nUnitIDCombine.2"],
        })

    with open("files/basic_data_init_final_v2/dechema/dechema_unit.json", "w", encoding="utf-8") as json_file:
        json.dump(unit_json, json_file, indent=4)
    # 相态对齐
    sheet_name_phase = "相态"
    phase_df = pd.read_excel(
        file_path, sheet_name=sheet_name_phase, header=0, dtype=str)
    phase_select = phase_df.iloc[:, :].fillna("")
    phase_json = []
    for index, row in phase_select.iterrows():
        phase_json.append({
            "dechema_phase": row["DETHERM_Phase"],
            "sei_phase_symbol": row["sPhaseSymbol"],
        })

    with open("files/basic_data_init_final_v2/dechema/dechema_phase.json", "w", encoding="utf-8") as json_file:
        json.dump(phase_json, json_file, indent=4)

    # 物性对齐

    sheet_name_property = "PropType+subType"
    property_df = pd.read_excel(
        file_path, sheet_name=sheet_name_property, header=0, dtype=str)
    property_select = property_df.iloc[:, :].fillna("")
    property_json = []
    for index, row in property_select.iterrows():
        property_json.append({
            "PropType": row["PropType"],
            "SubType": row["SubType"],
            "sei_property_abb": row["ePropAbbreviation"],
        })

    with open("files/basic_data_init_final_v2/dechema/dechema_property.json", "w", encoding="utf-8") as json_file:
        json.dump(property_json, json_file, indent=4)

    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    quantity_miss_entity = Quantity.objects.filter(
        quantity_name="未找到的量").first()
    if quantity_miss_entity is None:
        quantity_miss_entity = Quantity.objects.create(
            quantity_name="未找到的量",
            quantity_name_en="miss quantity",
            sei_id="miss",
            dimension=Dimension.objects.filter(dimension_name="无量纲").first(),
            quantity_description="创建量叫做未找到，标识物性绑定时没找到这个量",
            create_person=super_admin_entity,
            modify_person=super_admin_entity
        )
    # 先把物性全部当作变量创建进去
    # 获取所有property
    property_entity_list = Property.objects.filter(valid_flag=True).all()
    variable_list = []
    property_variable_list = []
    for item in property_json:
        name1 = "D_"+item["PropType"] + item["SubType"]
        variable_entity = PropertyVariable(
            name=name1,
            name_en=name1,
            symbol=name1,
            sei_id=name1,
            quantity=quantity_miss_entity,
            pure_used=True,
            binary_used=True,
            ternary_used=True,
            multi_used=True,
            constrain_used=True,
            component_related=True,
            description="",
            description_en="",
            create_person=super_admin_entity,
            modify_person=super_admin_entity,
        )
        variable_list.append(variable_entity)
        for property_entity in property_entity_list:
            property_variable_list.append(
                PropertyPropertyVariableMap(
                    property=property_entity,
                    property_variable=variable_entity
                )
            )
    try:
        with transaction.atomic():
            PropertyVariable.objects.bulk_create(variable_list)
            # 还需要将所有的物性都与这些变量进行绑定
            PropertyPropertyVariableMap.objects.bulk_create(
                property_variable_list)

    except Exception as e:
        print("error at property variable map")
        traceback.print_exc()
    # flag对齐

    sheet_name_flag = "Flag_set"
    flag_df = pd.read_excel(
        file_path, sheet_name=sheet_name_flag, header=0, dtype=str)
    flag_select = flag_df.iloc[:, :].fillna("")
    flag_json = []
    for index, row in flag_select.iterrows():
        phase_list = []
        if (row["sPhaseSymbol"] != ""):
            phase_list = row["sPhaseSymbol"]
        if (row["sPhaseSymbol2"] != ""):
            phase_list = row["sPhaseSymbol2"]
        if (row["sPhaseSymbol3"] != ""):
            phase_list = row["sPhaseSymbol3"]
        if (row["sPhaseSymbol4"] != ""):
            phase_list = row["sPhaseSymbol4"]

        flag_json.append({
            "dechema_flag": row["Flag_set"],
            "flag_phase": phase_list,
            "flag_comment": row["COMMENT"],
        })

    with open("files/basic_data_init_final_v2/dechema/dechema_flag.json", "w", encoding="utf-8") as json_file:
        json.dump(flag_json, json_file, indent=4)
    print("file convert to json finish")


def convert_dechema_pure_2_json():
    file_path = "files/basic_data_init_final_v2/dechema/Dechema_Pure_300.xlsx"
    sheet_name = "Sheet1"
    df = pd.read_excel(file_path, sheet_name=sheet_name,
                       header=0, dtype=str).fillna("")
    # 删除Note列是E的
    df = df[df["Note"] != "E"]
    # 按照Ref和DipprID分组
    grouped = df.groupby(
        ["Ref", "DipprID", "ePropAbbreviation", "Variable1Symbol", "Variable2Symbol"])

    # 将每组的数据转换为字典
    data_json = {}
    for (ref, dippr_id, propabb, variablie1, variable2), group in grouped:
        group_key = f"GROUP_{ref}_{dippr_id}_{propabb}_{variablie1}_{variable2}"
        group_info = {
            "group_key": group_key,
            "ref": ref,
            "dippr_id": dippr_id,
            "propabb": propabb,
            "variable1": variablie1,
            "variable2": variable2
        }
        group_content = group.to_dict(orient="records")
        group_info["content"] = group_content
        data_json[group_key] = group_info

    # 保存为JSON文件
    with open("files/basic_data_init_final_v2/dechema/dechema_pure.json", "w", encoding="utf-8") as json_file:
        json.dump(data_json, json_file, indent=4)


def convert_dechema_id_2_ref_id_json():

    unit_file_path = "files/basic_data_init_final_v2/A075. PCdata基表-3-参考文献表（20250403R1）-0版.xlsx"
    sheet_name = "文献总表"
    df = pd.read_excel(unit_file_path, sheet_name=sheet_name,
                       header=1, dtype=str)
    start_row = 1
    end_row = 124030
    # end_row = 13
    num_columns = 43
    selected_data = df.iloc[start_row:, :num_columns].fillna("")
    dechema_id_2_ref_id_json = {}
    for index, row in selected_data.iterrows():
        if row["DETHERM_ID"] != "" and row["DETHERM_ID"] != "NA":
            dechema_id_2_ref_id_json[row["DETHERM_ID"]] = row["nPCdataCitID"]
    with open(
        "files/basic_data_init_final_v2/dechema/dechema_id_2_ref_id.json", "w", encoding="utf-8"
    ) as json_file:
        json.dump(dechema_id_2_ref_id_json, json_file, indent=4)


def init_dechema_pure_data():
    # 读取JSON文件
    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    with open("files/basic_data_init_final_v2/dechema/dechema_pure.json", "r", encoding="utf-8") as json_file:
        data = json.load(json_file)
    # 获取dechema文献的映射表
    with open("files/basic_data_init_final_v2/dechema/dechema_id_2_ref_id.json", "r", encoding="utf-8") as json_file:
        dechema_id_2_ref_id = json.load(json_file)
    # 获取单位对齐表
    with open("files/basic_data_init_final_v2/dechema/dechema_unit.json", "r", encoding="utf-8") as json_file:
        unit_json = json.load(json_file)
    dechema_unit_dict = {}
    for item in unit_json:
        dechema_unit_dict[item["dechema_unit"]] = item["sei_unit_id"]
    # 获取相态对齐表
    with open("files/basic_data_init_final_v2/dechema/dechema_phase.json", "r", encoding="utf-8") as json_file:
        phase_json = json.load(json_file)
    dechema_phase_dict = {}
    for item in phase_json:
        dechema_phase_dict[item["dechema_phase"]] = item["sei_phase_symbol"]
    # 获取物性对齐表
    with open("files/basic_data_init_final_v2/dechema/dechema_property.json", "r", encoding="utf-8") as json_file:
        property_json = json.load(json_file)
    # 构造一个物性对齐表
    dechema_property_dict = {}
    for item in property_json:
        dechema_property_name = "DD_" + item["PropType"] + "--"
        dechema_property_name += item["SubType"]
        dechema_property_dict[dechema_property_name] = item["sei_property_abb"]
    # print("dechema_property_dict:", dechema_property_dict)
    # 获取flag对齐表
    with open("files/basic_data_init_final_v2/dechema/dechema_flag.json", "r", encoding="utf-8") as json_file:
        flag_json = json.load(json_file)
    flag_dict = {}
    for item in flag_json:
        flag_dict[item["dechema_flag"]] = {
            "phase": item["flag_phase"],
            "comment": item["flag_comment"]
        }

    # 获取数据库里的对应关系
    unit_dict = {}
    unit_entity_list = Unit.objects.filter(valid_flag=True).all()
    for unit_entity in unit_entity_list:
        unit_dict[unit_entity.sei_id] = unit_entity
    property_dict = {}
    property_entity_list = Property.objects.filter(valid_flag=True).all()
    for property_entity in property_entity_list:
        property_dict[property_entity.abbreviation] = property_entity
    phase_dict = {}
    phase_entity_list = Phase.objects.filter(valid_flag=True).all()
    for phase_entity in phase_entity_list:
        phase_dict[phase_entity.abbreviation] = phase_entity
    variable_dict = {}
    variable_entity_list = PropertyVariable.objects.filter(
        valid_flag=True).all()
    for variable_entity in variable_entity_list:
        variable_dict[variable_entity.symbol] = variable_entity
    data_present_unknow_entity = DataPresentWay.objects.filter(
        name="Unknown").first()
    if data_present_unknow_entity == None:
        data_present_unknow_entity = DataPresentWay.objects.create(
            name="Unknown", creator=super_admin_entity, last_editor=super_admin_entity
        )
    # dippr id 属性
    DSIID_attribute_entity = Attribute.objects.filter(name_en="nDSIID").first()
    if DSIID_attribute_entity is None:
        print("nDSIID attribute not exist")
        return
    data_present_unknow_entity = DataPresentWay.objects.filter(
        name="Unknown").first()
    if data_present_unknow_entity == None:
        data_present_unknow_entity = DataPresentWay.objects.create(
            name="Unknown", creator=super_admin_entity, last_editor=super_admin_entity
        )
    purpose_others_entity = ExpPurpose.objects.filter(name="others").first()
    if purpose_others_entity == None:
        purpose_others_entity = ExpPurpose.objects.create(
            name="others",
            creator=super_admin_entity,
            last_editor=super_admin_entity,
        )
    # 入库
    for index, (key, item) in enumerate(tqdm(data.items(), desc="Processing dechema pure data")):
        try:
            if index < -1:
                continue
            data_content = item["content"]
            if len(data_content) == 0:
                continue
            item_first = data_content[0]
            # 组分
            component_entity_attribute_map = ComponentAttributeValue.objects.filter(
                attribute=DSIID_attribute_entity,
                attribute_value_char=item_first["DSIID"],
            ).first()
            if component_entity_attribute_map is None:
                print("error at component dsi id :", item_first["DSIID"])
                continue
            else:
                component_entity = component_entity_attribute_map.componentidentifier
            # 文献
            if item_first["Ref"] == "":
                dechema_reference_entity = None
            else:
                ref_id = dechema_id_2_ref_id.get(item_first["Ref"], "")
                if ref_id == "":
                    dechema_reference_entity = None
                else:
                    dechema_reference_entity = Literature.objects.filter(
                        liter_code=str(ref_id)
                    ).first()
            # 物性 ePropAbbreviation
            property_name = "DD_"+item_first["ePropAbbreviation"]+"--"
            if item_first["PropSubType"] == "":
                property_name += "-"
            else:
                property_name += item_first["PropSubType"]
            property_entity = property_dict[dechema_property_dict[property_name]]
            # 物性单位
            property_unit_entity = unit_dict[dechema_unit_dict[item_first["PropUnit"]]]
            # 变量
            variable_name1 = "D_"
            if item_first["Variable1Symbol"] != "":
                variable_name1 += item_first["Variable1Symbol"]
            if item_first["Variable1PropSubType"] != "":
                variable_name1 += item_first["Variable1PropSubType"]
            else:
                variable_name1 += "-"
            if variable_name1 == "D_" or variable_name1 == "D_-":
                variable_entity1 = None
                variable_unit_entity1 = None
            else:
                variable_entity1 = variable_dict[variable_name1]
                variable_unit_entity1 = unit_dict[dechema_unit_dict[item_first["Variable1Unit"]]]
            # 变量2
            variable_name2 = "D_"
            if item_first["Variable2Symbol"] != "":
                variable_name2 += item_first["Variable2Symbol"]
            if item_first["Variable2PropSubType"] != "":
                variable_name2 += item_first["Variable2PropSubType"]
            else:
                variable_name2 += "-"
            if variable_name2 == "D_" or variable_name2 == "D_-":
                variable_entity2 = None
                variable_unit_entity2 = None
            else:
                variable_entity2 = variable_dict[variable_name2]
                variable_unit_entity2 = unit_dict[dechema_unit_dict[item_first["Variable2Unit"]]]
            # 相态
            description = ""
            comment = ""
            phase_list = set()
            if item_first["sPhaseSymbol"] != "":
                phase_list.add(item_first["sPhaseSymbol"])
            if item_first["sPhaseSymbol2"] != "":
                phase_list.add(item_first["sPhaseSymbol2"])
            if item_first["sPhaseSymbol3"] != "":
                phase_list.add(item_first["sPhaseSymbol3"])
            flag = item_first["FlagProp"]
            flags = flag.split(";")
            for flag1 in flags:
                flag_item = flag_dict.get(flag1, None)
                if flag_item is not None:
                    if flag_item["phase"] != []:
                        for phase2 in flag_item["phase"]:
                            phase_list.add(phase2)
                    if flag_item["comment"] != "":
                        comment = flag1+":"+flag_item["comment"]+"\n"
            dataset_name = item_first["Compound"] + "--" + item_first["Property"] + \
                "--" + item_first["Variable1Symbol"] + \
                ";" + item_first["Variable2Symbol"]
            # 入库
            with transaction.atomic():
                dataset_entity = ExpDataset.objects.create(
                    name=dataset_name,
                    zh_name=dataset_name,
                    description=description,
                    comment=comment,
                    source_liter=dechema_reference_entity,
                    purpose=purpose_others_entity,
                    data_present_way=data_present_unknow_entity,
                    review_status=1,
                    creator=super_admin_entity,
                    last_editor=super_admin_entity,
                )
                # 相态的集合
                phase_entity_list = []
                for phase_symbol in phase_list:
                    phase_entity = phase_dict.get(phase_symbol, None)
                    if phase_entity is not None:
                        phase_entity_list.append(phase_entity)
                if len(phase_entity_list) > 0:
                    dataset_entity.phases.set(phase_entity_list)
                # 设置组分
                ExpDatasetComponentMap.objects.create(
                    exp_dataset=dataset_entity, component=component_entity
                )
                ExpDatasetPropertyMap_entity = ExpDatasetPropertyMap.objects.create(
                    exp_dataset=dataset_entity,
                    property=property_entity,
                    unit=property_unit_entity,
                    measurement_error_percent=False,
                )
                # 变量
                if variable_entity1 is not None:
                    ExpDatasetVariableMap_entity1 = ExpDatasetVariableMap.objects.create(
                        exp_dataset=dataset_entity,
                        variable=variable_entity1,
                        unit=variable_unit_entity1,
                        measurement_error_percent=False,
                    )
                else:
                    ExpDatasetVariableMap_entity1 = None
                if variable_entity2 is not None:
                    ExpDatasetVariableMap_entity2 = ExpDatasetVariableMap.objects.create(
                        exp_dataset=dataset_entity,
                        variable=variable_entity2,
                        unit=variable_unit_entity2,
                        measurement_error_percent=False,
                    )
                else:
                    ExpDatasetVariableMap_entity2 = None
                # 要创建多个数据点了
                datapoint_entity_list = []
                datapoint_property_list = []
                datapoint_variable_list = []
                for point_item in data_content:
                    datapoint_entity1 = DataPoint()
                    datapoint_entity_list.append(datapoint_entity1)
                    # print("{}-{}-{}".format(
                    #     point_item["Variable1Value"],
                    #     point_item["Variable2Value"], point_item["PropValue"]))
                    if point_item["Variable1Value"] != "":
                        if point_item["Variable1Error"] != "":
                            datapoint_variable_entity1 = DataPointExpDatasetVariableMap(
                                data_point=datapoint_entity1,
                                exp_dataset_variable_map=ExpDatasetVariableMap_entity1,
                                value=point_item["Variable1Value"],
                                measurement_error_value=point_item["Variable1Error"],
                            )
                        else:
                            datapoint_variable_entity1 = DataPointExpDatasetVariableMap(
                                data_point=datapoint_entity1,
                                exp_dataset_variable_map=ExpDatasetVariableMap_entity1,
                                value=point_item["Variable1Value"],
                            )
                        datapoint_variable_list.append(
                            datapoint_variable_entity1)
                    if point_item["Variable2Value"] != "":
                        if point_item["Variable2Error"] != "":
                            datapoint_variable_entity2 = DataPointExpDatasetVariableMap(
                                data_point=datapoint_entity1,
                                exp_dataset_variable_map=ExpDatasetVariableMap_entity2,
                                value=point_item["Variable2Value"],
                                measurement_error_value=point_item["Variable2Error"],
                            )
                        else:
                            datapoint_variable_entity2 = DataPointExpDatasetVariableMap(
                                data_point=datapoint_entity1,
                                exp_dataset_variable_map=ExpDatasetVariableMap_entity2,
                                measurement_error_value=point_item["Variable2Value"],
                            )
                        datapoint_variable_list.append(
                            datapoint_variable_entity2)
                    if point_item["PropValue"] != "":
                        if point_item["Error"] != "":
                            datapoint_property_entity = DataPointExpDatasetPropertyMap(
                                data_point=datapoint_entity1,
                                exp_dataset_property_map=ExpDatasetPropertyMap_entity,
                                value=point_item["PropValue"],
                                measurement_error_value=point_item["Error"],
                            )
                        else:
                            datapoint_property_entity = DataPointExpDatasetPropertyMap(
                                data_point=datapoint_entity1,
                                exp_dataset_property_map=ExpDatasetPropertyMap_entity,
                                value=point_item["PropValue"],
                            )
                        datapoint_property_list.append(
                            datapoint_property_entity)
                DataPoint.objects.bulk_create(datapoint_entity_list)
                DataPointExpDatasetVariableMap.objects.bulk_create(
                    datapoint_variable_list)
                DataPointExpDatasetPropertyMap.objects.bulk_create(
                    datapoint_property_list)
                dataset_entity.data_points.set(datapoint_entity_list)

        except Exception as e:
            print("error at index:", index)
            print("error at group:", key)
            traceback.print_exc()
            continue
    print("dechema pure finish")


def convert_dechema_2_2_json():
    file_path = "files/basic_data_init_final_v2/dechema/Dechema_Comp2_300.xlsx"
    sheet_name = "Sheet1"
    df = pd.read_excel(file_path, sheet_name=sheet_name,
                       header=0, dtype=str).fillna("")
    # 删除Note列是E的
    df = df[df["Note"] != "E"]
    # 按照Ref和DipprID分组
    grouped = df.groupby(
        ["Ref", "DSIID1", "DSIID2", "Variable1Symbol", "Variable2Symbol", "PropSymbol", "Prop2Symbol"])

    # 将每组的数据转换为字典
    data_json = {}
    for (ref, dippr_id, dippr_id2, variablie1, variable2, propabb, propabb2), group in grouped:
        group_key = f"GROUP_{ref}_{dippr_id}_{dippr_id2}_{propabb}_{propabb2}_{variablie1}_{variable2}"
        group_info = {
            "group_key": group_key,
            "ref": ref,
            "dippr_id": dippr_id,
            "dippr_id2": dippr_id2,
            "propabb": propabb,
            "propabb2": propabb2,
            "variable1": variablie1,
            "variable2": variable2
        }
        group_content = group.to_dict(orient="records")
        group_info["content"] = group_content
        data_json[group_key] = group_info

    # 保存为JSON文件
    with open("files/basic_data_init_final_v2/dechema/dechema_2.json", "w", encoding="utf-8") as json_file:
        json.dump(data_json, json_file, indent=4)

    print("dechema 2 2 json finish")


def init_dechema_2_data():
    # 读取JSON文件
    super_admin_entity = User.objects.filter(user_account="superadmin").first()
    with open("files/basic_data_init_final_v2/dechema/dechema_2.json", "r", encoding="utf-8") as json_file:
        data = json.load(json_file)
    # 获取dechema文献的映射表
    with open("files/basic_data_init_final_v2/dechema/dechema_id_2_ref_id.json", "r", encoding="utf-8") as json_file:
        dechema_id_2_ref_id = json.load(json_file)
    # 获取单位对齐表
    # 获取单位对齐表
    with open("files/basic_data_init_final_v2/dechema/dechema_unit.json", "r", encoding="utf-8") as json_file:
        unit_json = json.load(json_file)
    dechema_unit_dict = {}
    for item in unit_json:
        dechema_unit_dict[item["dechema_unit"]] = item["sei_unit_id"]
    # 获取相态对齐表
    with open("files/basic_data_init_final_v2/dechema/dechema_phase.json", "r", encoding="utf-8") as json_file:
        phase_json = json.load(json_file)
    dechema_phase_dict = {}
    for item in phase_json:
        dechema_phase_dict[item["dechema_phase"]] = item["sei_phase_symbol"]
    # 获取物性对齐表
    with open("files/basic_data_init_final_v2/dechema/dechema_property.json", "r", encoding="utf-8") as json_file:
        property_json = json.load(json_file)
    # 构造一个物性对齐表
    dechema_property_dict = {}
    for item in property_json:
        dechema_property_name = "DD_" + item["PropType"] + "--"
        dechema_property_name += item["SubType"]
        dechema_property_dict[dechema_property_name] = item["sei_property_abb"]
    # print("dechema_property_dict:", dechema_property_dict)
    # 获取flag对齐表
    with open("files/basic_data_init_final_v2/dechema/dechema_flag.json", "r", encoding="utf-8") as json_file:
        flag_json = json.load(json_file)
    flag_dict = {}
    for item in flag_json:
        flag_dict[item["dechema_flag"]] = {
            "phase": item["flag_phase"],
            "comment": item["flag_comment"]
        }
    quantity_miss_entity = Quantity.objects.filter(
        quantity_name="未找到的量").first()
    if quantity_miss_entity is None:
        quantity_miss_entity = Quantity.objects.create(
            quantity_name="未找到的量",
            quantity_name_en="miss quantity",
            sei_id="miss",
            dimension=Dimension.objects.filter(dimension_name="无量纲").first(),
            quantity_description="创建量叫做未找到，标识物性绑定时没找到这个量",
            create_person=super_admin_entity,
            modify_person=super_admin_entity
        )
    Other_property_group_entity = PropertyGroup.objects.filter(
        name_en="Other").first()
    if Other_property_group_entity is None:
        Other_property_group_entity = PropertyGroup.objects.create(
            name="Other",
            name_en="Other",
            description="",
            description_en="",
            create_person=super_admin_entity,
            modify_person=super_admin_entity,
        )
    # 获取数据库里的对应关系
    unit_dict = {}
    unit_entity_list = Unit.objects.filter(valid_flag=True).all()
    for unit_entity in unit_entity_list:
        unit_dict[unit_entity.sei_id] = unit_entity
    property_dict = {}
    property_entity_list = Property.objects.filter(valid_flag=True).all()
    for property_entity in property_entity_list:
        property_dict[property_entity.abbreviation] = property_entity
    phase_dict = {}
    phase_entity_list = Phase.objects.filter(valid_flag=True).all()
    for phase_entity in phase_entity_list:
        phase_dict[phase_entity.abbreviation] = phase_entity
    variable_dict = {}
    variable_entity_list = PropertyVariable.objects.filter(
        valid_flag=True).all()
    for variable_entity in variable_entity_list:
        variable_dict[variable_entity.symbol] = variable_entity
    data_present_unknow_entity = DataPresentWay.objects.filter(
        name="Unknown").first()
    if data_present_unknow_entity == None:
        data_present_unknow_entity = DataPresentWay.objects.create(
            name="Unknown", creator=super_admin_entity, last_editor=super_admin_entity
        )
    # dippr id 属性
    DSIID_attribute_entity = Attribute.objects.filter(name_en="nDSIID").first()
    if DSIID_attribute_entity is None:
        print("nDSIID attribute not exist")
        return
    data_present_unknow_entity = DataPresentWay.objects.filter(
        name="Unknown").first()
    if data_present_unknow_entity == None:
        data_present_unknow_entity = DataPresentWay.objects.create(
            name="Unknown", creator=super_admin_entity, last_editor=super_admin_entity
        )
    purpose_others_entity = ExpPurpose.objects.filter(name="others").first()
    if purpose_others_entity == None:
        purpose_others_entity = ExpPurpose.objects.create(
            name="others",
            creator=super_admin_entity,
            last_editor=super_admin_entity,
        )

    # 入库
    error_num = 0
    for index, (key, item) in enumerate(tqdm(data.items(), desc="Processing dechema pure data")):
        try:
            if index < -1:
                continue
            data_content = item["content"]
            if len(data_content) == 0:
                continue
            item_first = data_content[0]
            # 组分
            component_entity_attribute_map = ComponentAttributeValue.objects.filter(
                attribute=DSIID_attribute_entity,
                attribute_value_char=item_first["DSIID1"],
            ).first()
            component_entity_attribute_map2 = ComponentAttributeValue.objects.filter(
                attribute=DSIID_attribute_entity,
                attribute_value_char=item_first["DSIID2"],
            ).first()
            if component_entity_attribute_map is None:
                print("error at component dsi id :", item_first["DSIID1"])
                continue
            if component_entity_attribute_map2 is None:
                print("error at component dsi id :", item_first["DSIID2"])
                continue

            component_entity1 = component_entity_attribute_map.componentidentifier
            component_entity2 = component_entity_attribute_map2.componentidentifier
            # 文献
            if item_first["Ref"] == "":
                dechema_reference_entity = None
            else:
                ref_id = dechema_id_2_ref_id.get(item_first["Ref"], "")
                if ref_id == "":
                    dechema_reference_entity = None
                else:
                    dechema_reference_entity = Literature.objects.filter(
                        liter_code=str(ref_id)
                    ).first()
            # 物性
            property_name1 = "DD_"+item_first["PropSymbol"]+"--"
            if item_first["PropSubType"] == "":
                property_name1 += "-"
            else:
                property_name1 += item_first["PropSubType"]
            if property_name1 == "DD_---":
                property_entity1 = None
                property_unit_entity1 = None
                property_phase_entity1 = None
            else:
                dechema_property_map_result = dechema_property_dict[property_name1]
                if dechema_property_map_result == "":
                    # 没有就创建这个物性
                    print("error at property name:", property_name1)
                    property_entity1 = Property.objects.filter(
                        abbreviation=property_name1).first()
                    if property_entity1 is None:
                        property_entity1 = Property.objects.create(
                            name=property_name1,
                            name_en=property_name1,
                            abbreviation=property_name1,
                            symbol=property_name1,
                            symbol_latex="",
                            property_group=Other_property_group_entity,
                            sei_id="",
                            symbol_mathml="",
                            description="",
                            quantity=quantity_miss_entity,
                            is_single_value=True,
                            is_fixed_value=True,
                            is_one_variable=True,
                            is_two_variable=True,
                            is_multi_variable=True,
                            is_reaction=True,
                            create_person=super_admin_entity,
                            modify_person=super_admin_entity,
                        )

                        dechema_property_dict[property_name1] = property_name1
                        property_dict[property_name1] = property_entity1
                    else:
                        dechema_property_dict[property_name1] = property_name1
                        property_dict[property_name1] = property_entity1

                property_entity1 = property_dict[dechema_property_dict[property_name1]]
                if item_first["PropUnit"] == "":
                    print("error at unit name: （", item_first["PropUnit"], "）")
                    raise Exception("unit error")
                
                property_unit_entity1 = unit_dict[dechema_unit_dict[item_first["PropUnit"]]]
                if item_first["PropPhase"] == "":
                    property_phase_entity1 = None
                else:
                    property_phase_entity1 = phase_dict[dechema_phase_dict[item_first["PropPhase"]]]

            property_name2 = "DD_"+item_first["Prop2Symbol"]+"--"
            if item_first["Prop2SubType"] == "":
                property_name2 += "-"
            else:
                property_name2 += item_first["Prop2SubType"]
            if property_name2 == "DD_---":
                property_entity2 = None
                property_unit_entity2 = None
                property_phase_entity2 = None
            else:
                dechema_property_map_result = dechema_property_dict[property_name2]
                if dechema_property_map_result == "":
                    # 没有就创建这个物性
                    print("error at property name:", property_name2)
                    property_entity2 = Property.objects.filter(
                        abbreviation=property_name2).first()
                    if property_entity2 is None:
                        property_entity2 = Property.objects.create(
                            name=property_name2,
                            name_en=property_name2,
                            abbreviation=property_name2,
                            symbol=property_name2,
                            symbol_latex="",
                            property_group=Other_property_group_entity,
                            sei_id="",
                            symbol_mathml="",
                            description="",
                            quantity=quantity_miss_entity,
                            is_single_value=True,
                            is_fixed_value=True,
                            is_one_variable=True,
                            is_two_variable=True,
                            is_multi_variable=True,
                            is_reaction=True,
                            create_person=super_admin_entity,
                            modify_person=super_admin_entity,
                        )

                        dechema_property_dict[property_name2] = property_name2
                        property_dict[property_name2] = property_entity2
                    else:
                        dechema_property_dict[property_name2] = property_name2
                        property_dict[property_name2] = property_entity2
                property_entity2 = property_dict[dechema_property_dict[property_name2]]
                if item_first["Prop2Unit"] == "":
                    print("error at unit name: （", item_first["Prop2Unit"], "）")
                    raise Exception("unit error")
                property_unit_entity2 = unit_dict[dechema_unit_dict[item_first["Prop2Unit"]]]
                if item_first["Prop2Phase"] == "":
                    property_phase_entity2 = None
                else:
                    property_phase_entity2 = phase_dict[dechema_phase_dict[item_first["Prop2Phase"]]]

            # 变量1
            variable_name1 = "D_"
            if item_first["Variable1Symbol"] != "":
                variable_name1 += item_first["Variable1Symbol"]
            if item_first["Variable1PropSubType"] != "":
                variable_name1 += item_first["Variable1PropSubType"]
            else:
                variable_name1 += "-"
            if variable_name1 == "D_" or variable_name1 == "D_-":
                variable_entity1 = None
                variable_unit_entity1 = None
            else:
                variable_entity1 = variable_dict[variable_name1]
                variable_unit_entity1 = unit_dict[dechema_unit_dict[item_first["Variable1Unit"]]]
            # 变量2
            variable_name2 = "D_"
            if item_first["Variable2Symbol"] != "":
                variable_name2 += item_first["Variable2Symbol"]
            if item_first["Variable2PropSubType"] != "":
                variable_name2 += item_first["Variable2PropSubType"]
            else:
                variable_name2 += "-"
            if variable_name2 == "D_" or variable_name2 == "D_-":
                variable_entity2 = None
                variable_unit_entity2 = None
            else:
                variable_entity2 = variable_dict[variable_name2]
                variable_unit_entity2 = unit_dict[dechema_unit_dict[item_first["Variable2Unit"]]]

            # 相态
            description = ""
            comment = ""
            phase_set = set()
            if item_first["sPhaseSymbol"] != "":
                phase_set.add(item_first["sPhaseSymbol"])
            if item_first["sPhaseSymbol2"] != "":
                phase_set.add(item_first["sPhaseSymbol2"])
            if item_first["sPhaseSymbol3"] != "":
                phase_set.add(item_first["sPhaseSymbol3"])
            if item_first["sPhaseSymbol4"] != "":
                phase_set.add(item_first["sPhaseSymbol4"])
            flag_set = set()
            flag1 = item_first["Flag1"]
            flag2 = item_first["Flag2"]
            flagProp = item_first["FlagProp"]
            flagProp2 = item_first["FlagProp2"]
            flags = flag1.split(";")
            for flag1 in flags:
                flag_set.add(flag1)
            flags = flag2.split(";")
            for flag2 in flags:
                flag_set.add(flag2)
            flags = flagProp.split(";")
            for flagProp in flags:
                flag_set.add(flagProp)
            flags = flagProp2.split(";")
            for flagProp2 in flags:
                flag_set.add(flagProp2)
            for flagt in flag_set:
                flag_item = flag_dict.get(flagt, None)
                if flag_item is not None:
                    if flag_item["phase"] != []:
                        for phase2 in flag_item["phase"]:
                            phase_set.add(phase2)
                    if flag_item["comment"] != "":
                        comment = flagt+":"+flag_item["comment"]+"\n"
            dataset_name = item_first["Compound1"] + "--" + item_first["Compound2"]+"--" + \
                item_first["Property"]+"--" + item_first["Variable1Symbol"] + \
                ";" + item_first["Variable2Symbol"]
            with transaction.atomic():
                dataset_entity = ExpDataset.objects.create(
                    name=dataset_name,
                    zh_name=dataset_name,
                    description=description,
                    comment=comment,
                    source_liter=dechema_reference_entity,
                    purpose=purpose_others_entity,
                    data_present_way=data_present_unknow_entity,
                    review_status=1,
                    creator=super_admin_entity,
                    last_editor=super_admin_entity,
                )
                # 相态的集合
                phase_entity_list = []
                for phase_symbol in phase_set:
                    phase_entity = phase_dict.get(phase_symbol, None)
                    if phase_entity is not None:
                        phase_entity_list.append(phase_entity)
                if len(phase_entity_list) > 0:
                    dataset_entity.phases.set(phase_entity_list)
                # 设置组分
                ExpDatasetComponentMap.objects.create(
                    exp_dataset=dataset_entity, component=component_entity1
                )
                ExpDatasetComponentMap.objects.create(
                    exp_dataset=dataset_entity, component=component_entity2
                )
                if property_entity1 is not None:
                    ExpDatasetPropertyMap_entity1 = ExpDatasetPropertyMap.objects.create(
                        exp_dataset=dataset_entity,
                        property=property_entity1,
                        unit=property_unit_entity1,
                        prop_phase=property_phase_entity1,
                        measurement_error_percent=False,
                    )
                else:
                    ExpDatasetPropertyMap_entity1 = None
                if property_entity2 is not None:
                    ExpDatasetPropertyMap_entity2 = ExpDatasetPropertyMap.objects.create(
                        exp_dataset=dataset_entity,
                        property=property_entity2,
                        unit=property_unit_entity2,
                        prop_phase=property_phase_entity2,
                        measurement_error_percent=False,
                    )
                else:
                    ExpDatasetPropertyMap_entity2 = None
                # 变量
                if variable_entity1 is not None:
                    ExpDatasetVariableMap_entity1 = ExpDatasetVariableMap.objects.create(
                        exp_dataset=dataset_entity,
                        variable=variable_entity1,
                        unit=variable_unit_entity1,
                        measurement_error_percent=False,
                    )
                else:
                    ExpDatasetVariableMap_entity1 = None
                if variable_entity2 is not None:
                    ExpDatasetVariableMap_entity2 = ExpDatasetVariableMap.objects.create(
                        exp_dataset=dataset_entity,
                        variable=variable_entity2,
                        unit=variable_unit_entity2,
                        measurement_error_percent=False,
                    )
                else:
                    ExpDatasetVariableMap_entity2 = None
                # 要创建多个数据点了
                datapoint_entity_list = []
                datapoint_property_list = []
                datapoint_variable_list = []
                for point_item in data_content:
                    datapoint_entity1 = DataPoint()
                    datapoint_entity_list.append(datapoint_entity1)
                    if point_item["Variable1Value"] != "":
                        if point_item["Variable1Error"] != "":
                            datapoint_variable_entity1 = DataPointExpDatasetVariableMap(
                                data_point=datapoint_entity1,
                                exp_dataset_variable_map=ExpDatasetVariableMap_entity1,
                                value=point_item["Variable1Value"],
                                measurement_error_value=point_item["Variable1Error"],
                            )
                        else:
                            datapoint_variable_entity1 = DataPointExpDatasetVariableMap(
                                data_point=datapoint_entity1,
                                exp_dataset_variable_map=ExpDatasetVariableMap_entity1,
                                value=point_item["Variable1Value"],
                            )
                        datapoint_variable_list.append(
                            datapoint_variable_entity1)
                    if point_item["Variable2Value"] != "":
                        if point_item["Variable2Error"] != "":
                            datapoint_variable_entity2 = DataPointExpDatasetVariableMap(
                                data_point=datapoint_entity1,
                                exp_dataset_variable_map=ExpDatasetVariableMap_entity2,
                                value=point_item["Variable2Value"],
                                measurement_error_value=point_item["Variable2Error"],
                            )
                        else:
                            datapoint_variable_entity2 = DataPointExpDatasetVariableMap(
                                data_point=datapoint_entity1,
                                exp_dataset_variable_map=ExpDatasetVariableMap_entity2,
                                measurement_error_value=point_item["Variable2Value"],
                            )
                        datapoint_variable_list.append(
                            datapoint_variable_entity2)
                    if point_item["PropValue"] != "":
                        if point_item["Error"] != "":
                            datapoint_property_entity = DataPointExpDatasetPropertyMap(
                                data_point=datapoint_entity1,
                                exp_dataset_property_map=ExpDatasetPropertyMap_entity1,
                                value=point_item["PropValue"],
                                measurement_error_value=point_item["Error"],
                            )
                        else:
                            datapoint_property_entity = DataPointExpDatasetPropertyMap(
                                data_point=datapoint_entity1,
                                exp_dataset_property_map=ExpDatasetPropertyMap_entity1,
                                value=point_item["PropValue"],
                            )
                        datapoint_property_list.append(
                            datapoint_property_entity)
                    if point_item["Prop2Value"] != "":
                        if point_item["Error2"] != "":
                            datapoint_property_entity = DataPointExpDatasetPropertyMap(
                                data_point=datapoint_entity1,
                                exp_dataset_property_map=ExpDatasetPropertyMap_entity2,
                                value=point_item["Prop2Value"],
                                measurement_error_value=point_item["Error2"],
                            )
                        else:
                            datapoint_property_entity = DataPointExpDatasetPropertyMap(
                                data_point=datapoint_entity1,
                                exp_dataset_property_map=ExpDatasetPropertyMap_entity2,
                                value=point_item["Prop2Value"],
                            )
                        datapoint_property_list.append(
                            datapoint_property_entity)
                DataPoint.objects.bulk_create(datapoint_entity_list)
                DataPointExpDatasetVariableMap.objects.bulk_create(
                    datapoint_variable_list)
                DataPointExpDatasetPropertyMap.objects.bulk_create(
                    datapoint_property_list)
                dataset_entity.data_points.set(datapoint_entity_list)

        except Exception as e:
            print("error at index:", index)
            print("error at group:", key)
            traceback.print_exc()
            error_num += 1
            print("error num:", error_num)
            continue

    print("dechema 2 data finish")


def init_dechema_content():
    convert_file_to_json()
    # 获取dechema的文献映射表
    convert_dechema_id_2_ref_id_json()
    convert_dechema_pure_2_json()
    init_dechema_pure_data()
    convert_dechema_2_2_json()
    init_dechema_2_data()

    print("dechema数据入库完成")
